package dbTest;
import java.sql.*;

public class jdbc2 {
    // JDBC driver name and database URL
    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
    static final String DB_URL = "jdbc:mysql://localhost:3306/online_store";

    // Database credentials
    static final String USER = "root";
    static final String PASS = "racksys123";

    //插入的数据条数
    static final int InsertCounter = 1000000;

    public static void main(String[] args) {
        Connection connection = null;
        PreparedStatement statement = null;
        Long startTime = 0L;
        Long endTime = 0L;

        try{
            //STEP 2: Register JDBC driver
            Class.forName(JDBC_DRIVER);

            //STEP 3: Open a connectionection
            connection = DriverManager.getConnection(DB_URL, USER, PASS);

            //STEP 4: Execute a query
            //这里需要注意，SQL语句的格式必须是预处理的这种，就是values(?,?,...,?)，否则批处理不起作用
            statement = connection.prepareStatement("INSERT INTO product (pid,pname,price) VALUES (?, ?, ?)");

            System.out.println("开始插入数据");
            startTime = System.currentTimeMillis();

            for (int i = 0; i < InsertCounter ; i++) {
                statement.setLong(1, i);
                statement.setString(2, "商品" + i);
                statement.setInt(3, i);
                //将要执行的SQL语句先添加进去，不执行
                statement.addBatch();
            }

            //100W条SQL语句已经添加完成，执行这100W条命令并提交
            statement.executeBatch();
            connection.commit();

        }catch(SQLException e){
            //Handle errors for JDBC
            e.printStackTrace();
        }catch(Exception e){
            //Handle errors for Class.forName
            e.printStackTrace();
        }finally{
            //finally block used to close resources
            try{
                if(statement != null){
                    connection.close();
                }
            }catch(SQLException e){

            }
            try{
                if(connection != null){
                    connection.close();
                }
            }catch(SQLException e){
                e.printStackTrace();
            }
        }

        endTime = System.currentTimeMillis();
        System.out.println(InsertCounter + "条数据插入完毕,用时：" + (endTime - startTime));
    }
}
